
/*
The purpose of this .do file is to test suggestions Frank Vella gave me 
in a meeting on 11/3/17 
Steps: 
1. 	Import the data and generate some extra controls 
2. 	Run The pooled regressions of rent & util exp on energy start cert 
*/


*Set Directories 
clear all 
set more off 
macro drop _all 

*Set Directories
global NCREIF 	"C:\Users\bmb105\Dropbox\2019\NCREIF\Stata" 
global Fake 	"$NCREIF\FakeFiles" 
global Results 	"$NCREIF\Results"
global GenData 	"$NCREIF\Generated_Data"
global Graphs 	"$Results\Figures"

*For Margaret dropbox folders 
global NCREIF "/Users/BeckaBrolinson/Dropbox/NCREIF/data" 
global build 	"$NCREIF/build" 
global analysis "$NCREIF/analysis"
global results 	"$analysis/results"
global figures 	"$analysis/figures" 


*------------------------------------------------------------------------------*
*	Step 1- summary statistics	   *
*------------------------------------------------------------------------------*
{


use "$build/01_annualizeddatawcontrol.dta", replace

*Control variables (So that I can test and compare covariate balance with different controls 
	#d ;
	global Cov1 	"Covered_E real_elecprice real_gasprice Unemployment HDD CDD 
	age2 age used_space
	 percentleased real_capex_ti real_capex_bldimp "; 
	global Cov2 	"Covered_E real_elecprice real_gasprice Unemployment HDD CDD 
	 age age2 percentleased used_space
	dFundType1 dFundType2 dFundType4 dFundType5 dFundType6";
	global Cov3	"Covered_E real_elecprice Unemployment HDD CDD 
	age2 age used_space"; 
	global num	"3";	
	global matrow	"7";
	global matcol	"9";
	#d cr
*Summary Statistics 

	*Generate price summary statistics 
	#d ; 
	global var "rl_yr_utilpersf rl_yr_rentpersf sqft yrbuilt Covered_E 
				real_elecprice real_gasprice Unemployment HDD CDD age used_space
				percentleased real_capex_ti real_capex_bldimp dFundType1
				dFundType2 dFundType3 dFundType4 dFundType5 dFundType6"; 
	
	#d cr 	
	*Make list of covariates 
		 
		
	
	*Inputs 
	local meanrow "3" 
	local sdrow "4"
	local obsrow "5"
	
	putexcel set "$results/SummaryStat.xlsx", sheet("SummaryStats") modify
	foreach var of global var{
	su `var' if treat==0 & year<=2009, detail
	local ++meanrow
	local ++sdrow
	putexcel B3=("Mean Uncertified Pre-2009") A`meanrow'=("`var'") B`meanrow'=(`r(mean)') B`sdrow'=(`r(sd)')
	local ++meanrow 
	local ++sdrow
	} 
	
	*Inputs 
	local meanrow "3" 
	local sdrow "4"
	local obsrow "5"
	
	putexcel set "$results/SummaryStat.xlsx", sheet("SummaryStats") modify
	foreach var of global var{
	su `var' if treat==0 & year>2009, detail
	local ++meanrow
	local ++sdrow
	putexcel C3=("Mean Uncertified Post-2009") C`meanrow'=(`r(mean)') C`sdrow'=(`r(sd)')
	local ++meanrow 
	local ++sdrow
	} 
	
	*Inputs 
	local meanrow "3" 
	local sdrow "4"
	local obsrow "5"
	
	putexcel set "$results/SummaryStat.xlsx", sheet("SummaryStats") modify
	foreach var of global var{
	su `var' if treat==1 & post==0 , detail 
	local ++meanrow
	local ++sdrow
	putexcel D3=("Mean Certified Pre-Cert") D`meanrow'=(`r(mean)') D`sdrow'=(`r(sd)')
	local ++meanrow 
	local ++sdrow
	} 
	
	*Inputs 
	local meanrow "3" 
	local sdrow "4"
	local obsrow "5"
	
	putexcel set "$results/SummaryStat.xlsx", sheet("SummaryStats") modify
	foreach var of global var{
	su `var' if treat==1 & post==1 , detail 
	local ++meanrow
	local ++sdrow
	putexcel E3=("Mean Certified Post-Cert") E`meanrow'=(`r(mean)') E`sdrow'=(`r(sd)')
	local ++meanrow 
	local ++sdrow
	} 
	
	
	use "$build/01_annualizeddatawcontrol.dta", replace


*Generate a dummy variable if year is before 2009 to generate pre/post summary statistics 
	gen d_pre2009 = (year<=2009)

	*Generate the dataset that is the summary statistics dataset 
	collapse (mean) rl_yr_utilpersf_1 = rl_yr_utilpersf rl_yr_rentpersf_1 = rl_yr_rentpersf (sd) rl_yr_utilpersf_2 = rl_yr_utilpersf rl_yr_rentpersf_2 = rl_yr_rentpersf, by(treat d_pre2009) 

	*round to two decimal places 
	format _all %12.2fc 
	*local n = _N 
	*di `n' 
	foreach var of varlist *{
	replace `var'=  round(`var', 0.01)
	}
	foreach var of varlist *{
	replace `var'=  round(`var', 0.01)
	}
	foreach var of varlist *{
	capture replace `var'=  round(`var', 0.01)
	}
			
	*Stack sd and mean 
	reshape long rl_yr_utilpersf_ rl_yr_rentpersf_ , i(treat d_pre2009) j(type)
	tostring *, replace force
	
	*reshape 
	gen col_num = "." 
	replace col_num = "Cert Pre" if treat ==1 & d_pre2009 == 1 
	replace col_num = "Cert Post" if treat ==1 & d_pre2009 == 0 
	replace col_num = "Uncert Pre" if treat ==0 & d_pre2009 == 1 
	replace col_num = "Uncert Post" if treat ==0 & d_pre2009 == 0 
	
	xpose, clear varname 
	
	rename v4 CertPre
	rename v3 CertPost
	rename v2 UncertPre
	rename v1 UncertPost
	
	foreach var of varlist *{
	capture replace `var'=  round(`var', 0.01)
	}
	format UncertPost %15.0fc

	

